#!/bin/bash
Backup_Databases_tables(){
bin_Path="/usr/local/mysql/bin"
my_user="root"
my_pass="666666"
backup_Dir="/opt"
socket_file="/tmp/mysql.sock"

#取库名
dbList=`mysql -u$my_user -p$my_pass -S ${socket_file} -e "show databases;"\
        | egrep -vi "Database|information_schema|mysql|performance_schema|sys"`
        #|egrep -v "Database|information_schema|mysql|performance_schema|sys"`
echo "所需要的库"
echo "$dbList"	

for db in $dbList
do
    #取库中的表名
    table=`mysql -u$my_user -p$my_pass -S ${socket_file} -e "show tables from $db"|sed '1d'`
	echo "${db}库下的所有表"
	echo "${table}"
	
	for tb in $table
	do
		echo "创建以库命名的目录"
		echo "创建${backup_Dir}/${db}"
		[ -d ${backup_Dir}/${db} ] || mkdir -p ${backup_Dir}/${db}
		
		echo "即将开始备份$db库下$tb表"
		${bin_Path}/mysqldump -u$my_user -p$my_pass -S ${socket_file} -F \
			--compact --master-data=2 --single-transaction --events $db $tb | \
			gzip > $backup_Dir/${db}/mysql_backup_${db}-${tb}-$(date +%Y%m%d-%H-%M-%S).sql.gz
		if [ $? = 0 ];then
	        echo "${db}库下${tb}表备份成功"
		    sleep 6
	    else
	        echo "${db}库下${tb}表备份失败，即将退出备份"
            exit 22
		    sleep 6
	    fi

	done
done
}
